package com.logisticsmanagement.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.logisticsmanagement.domain.DeliveryStation;
import com.logisticsmanagement.domain.Indent;
import com.logisticsmanagement.domain.Route;
import com.logisticsmanagement.domain.Truck;
import com.logisticsmanagement.utils.DataSource;
import com.logisticsmanagement.utils.DatabaseUtil;

public class TruckDao implements Serializable{
	
	/**
	 * get Truck through special truckID
	 * @param truckID
	 * @return
	 */
	public Truck selectByID(int truckID){
		
		Truck truck=null;
		Connection conn = null;
		Indent indent =null;
		try {
			conn= DataSource.getConnection();
			if(conn == null)
				return truck;
			PreparedStatement sta = conn.prepareStatement(
					"select * from truck where truckID=?");
			sta.setInt(1, truckID);
			ResultSet rs = sta.executeQuery();
			if(rs.next()){
				truck=new Truck();
				truck.setTruckID(rs.getInt("truckID"));
				truck.setFromCityID(rs.getInt("fromCityID"));
				truck.setOriginCityID(rs.getInt("originCityID"));
				truck.setDriverID(rs.getString("driverID"));
				truck.setNextCityID(rs.getInt("nextCityID"));
				truck.setCarNumber(rs.getString("carNumber"));
				truck.setTruckPrice(rs.getDouble("truckPrice"));
				truck.setPrepareDelete(rs.getBoolean("prepareDelete"));
				truck.setIsDelete(rs.getBoolean("isDelete"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		return truck;	
		
	}

	
/*	public Boolean deleteByID(Integer truckID){
		
//		Truck truck=null;
		Connection conn = null;
		Indent indent =null;
		try {
			conn= DataSource.getConnection();
			if(conn == null)
				return null;
			PreparedStatement sta = conn.prepareStatement(
					"update truck set isDelete=true where truckID=?");
			sta.setInt(1, truckID);
			ResultSet rs = sta.executeQuery();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		return false;
	}*/

	/**
	 * update Truck info.
	 * @param truck
	 * @return
	 */
	public Boolean update(Truck truck){
		
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return false;
			sta = conn.prepareStatement(
 					"update truck set fromCityID=? and "
					+ "originCityID=? and driverID=? and nextCityID=? and "
					+ "carNumber=? and truckPrice=? and prepareDelete=? and isDelete=? where truckID=?");	//get operation statement object of database
			
			sta.setInt(1, truck.getFromCityID());
			sta.setInt(2, truck.getOriginCityID());
			sta.setString(3, truck.getDriverID());
			sta.setInt(4, truck.getNextCityID());
			sta.setString(5, truck.getCarNumber());
			sta.setDouble(6, truck.getTruckPrice());
			sta.setBoolean(7, (truck.getPrepareDelete() == true)?true:false);
			sta.setBoolean(8, (truck.getIsDelete() == true)?true:false);
			sta.executeUpdate();	//execute，update database
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}

	/**
	 * add truck
	 * @param truck
	 */
	public Boolean add(Truck truck){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to object
			if(conn == null)
				return null;
			sta = conn.prepareStatement(
					"insert into truck(truckID,fromCityID,originCityID,"
					+ "driverID,nextCityID,carNumber,truckPrice,prepareDelete,isDelete) "
					+ "values(?,?,?,?,?,?,?,?,?)");	 //get operation statement object of database
			sta.setInt(1, truck.getTruckID());
			sta.setInt(2, truck.getFromCityID());
			sta.setInt(3, truck.getOriginCityID());
			sta.setString(4, truck.getDriverID());
			sta.setInt(5, truck.getNextCityID());
			sta.setString(6, truck.getCarNumber());
			sta.setDouble(7, truck.getTruckPrice());
			sta.setBoolean(8, (truck.getPrepareDelete()==true)?true:false);
			sta.setBoolean(9, (truck.getIsDelete()==true)?true:false);
			return (sta.executeUpdate()==1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * count amount of truck with certain attribute
	 * @param truck
	 * @return
	 */
	public Integer selectCount(Truck truck){
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			sta = DatabaseUtil.getSelcectCountStatement(truck, conn);	//define sql query statement, then get operation statement object of database
			rs = sta.executeQuery();		//query database, return ResultSet
			if(rs.next()){
				return rs.getInt(1);
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try { 	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return null;
	}
	
	/**
	 * the criteria query,the paging query 
	 * @param truck
	 * @param nowPage
	 * @param pageSize
	 * @return
	 */
 	public List<Truck> selectPage(Truck truck,int nowPage,int pageSize, String orderBy){
		
 		List<Truck> ret = new ArrayList<Truck>();
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		if(orderBy == (null))
			orderBy = "truckID";
		
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			//define sql query statement, then get operation statement object of database
			sta = DatabaseUtil.getSelectPageStatment(truck, conn, nowPage, pageSize, orderBy);	 
			rs = sta.executeQuery();		//query database, return ResultSet
			//traverse packaged object
			while(rs.next()){
				Truck retTruck =  new Truck();
				retTruck.setTruckID(rs.getInt(1));
				retTruck.setFromCityID(rs.getInt(2));
				retTruck.setOriginCityID(rs.getInt(3));
				retTruck.setDriverID(rs.getString(4));
				retTruck.setNextCityID(rs.getInt(5));
				retTruck.setCarNumber(rs.getString(6));
				retTruck.setTruckPrice(rs.getDouble(7));
				retTruck.setPrepareDelete(rs.getBoolean(8));
				retTruck.setIsDelete(rs.getBoolean(9));
				ret.add(retTruck);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return ret;
	}
}
